
import pymysql
import pandas as pd

def get_database_data():
    """从数据库获取订单数据"""
    # 打开数据库连接
    db = pymysql.connect(
        host="localhost",
        user="root",
        password="hui123456",
        db='dbtest')
    cursor = db.cursor()

    try:
        sql = """
            SELECT order2, product_BianMa  
            FROM doudian_day 
            WHERE MONTH(day) = 9 
                AND (order_zt = "已签收" OR order_zt = "待发货" OR order_zt = "已完成" 
                     OR order_zt = "已发货" OR order_zt = "已支付")
                AND order_sh != "退款成功"  
                AND order_sh != "售后完成" 
                AND order_sh != "退款完成"  
                AND order_sh != "已全额退款" 
                AND order_sh != "退货退款完成"
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        dou = pd.DataFrame(result)
        dou.columns = ['订单id', '商家编码']
        return dou
    finally:
        cursor.close()
        db.close()


def load_commission_data():
    """加载佣金数据"""
    commission_files = [
        r'G:\工作\2025年订单\9月\佣金1.xlsx',
        r'G:\工作\2025年订单\9月\佣金2.xlsx',
        r'G:\工作\2025年订单\9月\佣金3.xlsx',
        r'G:\工作\2025年订单\9月\佣金4.xlsx'
    ]

    commission_dfs = []
    for file_path in commission_files:
        df = pd.read_excel(file_path, dtype={
            '订单id': str,
            '商品id': str,
            '作者账号': str,
            '抖音/火山号': str
        })
        commission_dfs.append(df)

    y = pd.concat(commission_dfs)
    y['订单id'] = y['订单id'].str.strip()
    return y[y['订单状态'] != '订单退货退款']


def load_promotion_data():
    """加载招商数据"""
    promotion_files = [
        r'G:\工作\2025年订单\9月\招商1.xlsx',
        r'G:\工作\2025年订单\9月\招商2.xlsx',
        r'G:\工作\2025年订单\9月\招商3.xlsx',
        r'G:\工作\2025年订单\9月\招商4.xlsx'
    ]

    promotion_dfs = []
    for file_path in promotion_files:
        df = pd.read_excel(file_path, dtype={'订单id': str})
        promotion_dfs.append(df)

    z = pd.concat(promotion_dfs)
    z['订单id'] = z['订单id'].str.strip()
    return z.loc[:, ['订单id', '服务费率', '出单机构']]


def main():
    """主函数"""
    # 获取数据库数据
    dou = get_database_data()

    # 加载佣金数据
    y1 = load_commission_data()

    # 加载招商数据
    z_date = load_promotion_data()

    # 匹配商家编码
    result_1 = pd.merge(y1, dou, on='订单id', how='left')

    # 匹配招商团长
    result_2 = pd.merge(result_1, z_date, on='订单id', how='left')

    # 保存结果
    result_2.to_excel(r'G:\工作\2025年订单\9月\绩效\9月招商提成明细.xlsx', index=False)


if __name__ == "__main__":
    main()
